package org.cncitrus.data.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;

public class ChinaImportConn {
	private String TABLE_NAME = "orange_z_jinliang";
	private Statement stmt;

	public ChinaImportConn() throws SQLException {
		stmt = OrangeConn.getInstance().getStatement();
	}

	public String load(String[] year, String[] code) throws Exception {
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE "+Params.ChinaImport.year+" IN "
				+ ParamsManager.Array2String(year) + " AND "+Params.ChinaImport.code+" IN "
				+ ParamsManager.Array2String(code);
		ResultSet rs = stmt.executeQuery(sql);
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		rs.last();
		sb.append("<resp count=\"" + rs.getRow() + "\"> ");
		rs.beforeFirst();
		while (rs.next()) {
			sb.append("<item year=\"" + rs.getString(Params.ChinaImport.year).trim()
					+ "\" code=\"" + rs.getString(Params.ChinaImport.code).trim() + "\""
					+ " country=\"" + rs.getString(Params.ChinaImport.country).trim() + "\""
					+ " total=\"" + rs.getString(Params.ChinaImport.total).trim() + "\""
					+ " money=\"" + rs.getString(Params.ChinaImport.money).trim() + "\" />");
		}
		sb.append("</resp>");
		return sb.toString();
	}
	
	public String insert(String[] year,String[] code,String[] county,String[] money,String[] total) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.ChinaImport.year+","+Params.ChinaImport.code+","+Params.ChinaImport.country+","+Params.ChinaImport.money+","+Params.ChinaImport.total+") VALUES ");		
		
		String content = ParamsManager.getInsertString(year,code,county,money,total);
		sb.append(content);
		stmt.execute(sb.toString());
		sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<resp rc=\"0\"/>");		
		return sb.toString();
	}
	
}
